
if exists (select 1 from sysobjects where name = 'get_personnotinschema' and type = 'P')
begin
   drop procedure get_personnotinschema
   print 'Procedure: get_personnotinschema deleted ...'
end
go
create procedure get_personnotinschema(
  @schemaid int = 1
)
as
begin
  set nocount on

  select p.prsid,
         a.abteilungid,
         p.vorname,
         p.nachname,
         a.name

    from abteilung a
    left outer join personalabteilung pa
      on a.abteilungid = pa.abteilungid
    left outer join Person p
     on p.prsid = pa.prsid
    and p.persontyp = 1
  where a.abteilungid in (select ps.abteilungid from planschema ps where ps.schemaid = @schemaid)
    and p.prsid not in (select ps.prsid from planschema ps where ps.schemaid = @schemaid)
    and pa.bis = (select max(pa2.bis) from personalabteilung pa2 where pa2.prsid = p.prsid and a.abteilungid = pa2.abteilungid)

end
go
print 'Procedure: get_personnotinschema done ...'
go

grant exec on get_personnotinschema to prsadmins with grant option
go
grant exec on get_personnotinschema to prsusers
go

